﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using Enter.DataProxy.DbConnector.EntLib;
using Enter.DataCommon;


namespace Enter.DataProxy.DbConnector
{
    /// <summary>
    /// 企业库Database扩展
    /// </summary>
    public static class DatabaseExtensions
    {
        /// <summary>
        /// 执行sql语句返回一个IDataReader
        /// </summary>
        /// <param name="db">Microsoft.Practices.EnterpriseLibrary.Data.Database</param>
        /// <param name="type">命令类型</param>
        /// <param name="commandText">sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <returns>返回一个IDataReader</returns>
        public static IDataReader ExecuteReader(this Database db, CommandType type, string commandText, ParameterCollection parameters)
        {
            DbCommand command = GetCommand(db, type, commandText);
            if (parameters != null)
            {
                command.Parameters.AddRange(parameters.ToArray());
            }
            return db.ExecuteReader(command);
        }

        /// <summary>
        /// 执行sql语句返回一个IDataReader
        /// </summary>
        /// <param name="db">Microsoft.Practices.EnterpriseLibrary.Data.Database</param>
        /// <param name="type">命令类型</param>
        /// <param name="commandText">sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="transaction">事务处理</param>
        /// <returns>返回一个IDataReader</returns>
        public static IDataReader ExecuteReader(this Database db, CommandType type, string commandText, ParameterCollection parameters, DbTransaction transaction)
        {
            DbCommand command = GetCommand(db, type, commandText);
            if (parameters != null)
            {
                command.Parameters.AddRange(parameters.ToArray());
            }
            return db.ExecuteReader(command, transaction);
        }

        /// <summary>
        /// 执行sql语句返回一个DataSet
        /// </summary>
        /// <param name="db">Microsoft.Practices.EnterpriseLibrary.Data.Database</param>
        /// <param name="commandText">sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <returns>返回一个DataSet</returns>
        public static DataSet ExecuteDataSet(this Database db, CommandType type, string commandText, ParameterCollection parameters)
        {
            DbCommand command = GetCommand(db, type, commandText);
            if (parameters != null)
            {
                command.Parameters.AddRange(parameters.ToArray());
            }
            return db.ExecuteDataSet(command);
        }

        /// <summary>
        /// 执行sql语句返回一个DataSet
        /// </summary>
        /// <param name="db">Microsoft.Practices.EnterpriseLibrary.Data.Database</param>
        /// <param name="commandText">sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="transaction">事务处理</param>
        /// <returns>返回一个DataSet</returns>
        public static DataSet ExecuteDataSet(this Database db, CommandType type, string commandText, ParameterCollection parameters, DbTransaction transaction)
        {
            DbCommand command = GetCommand(db, type, commandText);
            if (parameters != null)
            {
                command.Parameters.AddRange(parameters.ToArray());
            }
            return db.ExecuteDataSet(command, transaction);
        }

        /// <summary>
        /// 执行sql语句，不返回结果集
        /// </summary>
        /// <param name="db">Microsoft.Practices.EnterpriseLibrary.Data.Database</param>
        /// <param name="type">命令类型</param>
        /// <param name="commandText">sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteNonQuery(this Database db, CommandType type, string commandText, ParameterCollection parameters)
        {
            DbCommand command = GetCommand(db, type, commandText);
            if (parameters != null)
            {
                command.Parameters.AddRange(parameters.ToArray());
            }
            return db.ExecuteNonQuery(command);
        }

        /// <summary>
        /// 执行sql语句，不返回结果集
        /// </summary>
        /// <param name="db">Microsoft.Practices.EnterpriseLibrary.Data.Database</param>
        /// <param name="type">命令类型</param>
        /// <param name="commandText">sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="transaction">事务处理</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteNonQuery(this Database db, CommandType type, string commandText, ParameterCollection parameters, DbTransaction transaction)
        {
            DbCommand command = GetCommand(db, type, commandText);
            if (parameters != null)
            {
                command.Parameters.AddRange(parameters.ToArray());
            }
            return db.ExecuteNonQuery(command, transaction);
        }


        ///// <summary>
        ///// 执行sql语句，不返回任何结果集(批量执行,没有事物支持不能保证数据的一致性和完整性)
        ///// </summary>
        ///// <param name="db">Microsoft.Practices.EnterpriseLibrary.Data.Database</param>
        ///// <param name="batch">sql批次</param>
        ///// <returns>受影响的行数</returns>
        //public static int ExecuteNonQuery(this Database db, BatchSQL batch)
        //{
        //    StringBuilder sql = new StringBuilder();
        //    foreach (string sqlStatment in batch)
        //    {
        //        sql.AppendLine(sqlStatment);
        //    }
        //    DbCommand command = db.GetSqlStringCommand(sql.ToString());
        //    command.Parameters.AddRange(batch.Parameters.ToArray());

        //    return db.ExecuteNonQuery(command);
        //}

        ///// <summary>
        ///// 执行sql语句，不返回任何结果集(批量执行,没有事物支持不能保证数据的一致性和完整性)
        ///// </summary>
        ///// <param name="db">Microsoft.Practices.EnterpriseLibrary.Data.Database</param>
        ///// <param name="batch">sql批次</param>
        ///// <param name="transaction">事务处理</param>
        ///// <returns>受影响的行数</returns>
        //public static int ExecuteNonQuery(this Database db, BatchSQL batch, DbTransaction transaction)
        //{
        //    StringBuilder sql = new StringBuilder();
        //    foreach (string sqlStatment in batch)
        //    {
        //        sql.AppendLine(sqlStatment);
        //    }
        //    DbCommand command = db.GetSqlStringCommand(sql.ToString());
        //    command.Parameters.AddRange(batch.Parameters.ToArray());

        //    return db.ExecuteNonQuery(command, transaction);
        //}

        ///// <summary>
        ///// 执行sql语句，返回DataSet(批量执行,没有事物支持不能保证数据的一致性和完整性)
        ///// </summary>
        ///// <param name="db">Microsoft.Practices.EnterpriseLibrary.Data.Database</param>
        ///// <param name="batch">sql批次</param>
        ///// <returns>返回一个DataSet</returns>
        //public static DataSet ExecuteDataSet(this Database db, BatchSQL batch)
        //{
        //    StringBuilder sql = new StringBuilder();
        //    foreach (string sqlStatment in batch)
        //    {
        //        sql.AppendLine(sqlStatment);
        //    }
        //    DbCommand command = db.GetSqlStringCommand(sql.ToString());
        //    command.Parameters.AddRange(batch.Parameters.ToArray());

        //    return db.ExecuteDataSet(command);
        //}

        ///// <summary>
        ///// 执行sql语句，返回DataSet(批量执行,没有事物支持不能保证数据的一致性和完整性)
        ///// </summary>
        ///// <param name="db">Microsoft.Practices.EnterpriseLibrary.Data.Database</param>
        ///// <param name="batch">sql批次</param>
        ///// <param name="transaction">事务处理</param>
        ///// <returns>返回一个DataSet</returns>
        //public static DataSet ExecuteDataSet(this Database db, BatchSQL batch, DbTransaction transaction)
        //{
        //    StringBuilder sql = new StringBuilder();
        //    foreach (string sqlStatment in batch)
        //    {
        //        sql.AppendLine(sqlStatment);
        //    }
        //    DbCommand command = db.GetSqlStringCommand(sql.ToString());
        //    command.Parameters.AddRange(batch.Parameters.ToArray());

        //    return db.ExecuteDataSet(command, transaction);
        //}

        /// <summary>
        /// 执行sql语句，返回标量
        /// </summary>
        /// <param name="db">Microsoft.Practices.EnterpriseLibrary.Data.Database</param>
        /// <param name="type">命令类型</param>
        /// <param name="commandText">sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <returns>受影响的行数/或第一行第一列的值</returns>
        public static object ExecuteScalar(this Database db, CommandType type, string commandText, ParameterCollection parameters)
        {
            DbCommand command = GetCommand(db, type, commandText);
            if (parameters != null)
            {
                command.Parameters.AddRange(parameters.ToArray());
            }
            return db.ExecuteScalar(command);
        }

        /// <summary>
        /// 执行sql语句，返回标量
        /// </summary>
        /// <param name="db">Microsoft.Practices.EnterpriseLibrary.Data.Database</param>
        /// <param name="type">命令类型</param>
        /// <param name="commandText">sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="transaction">事务处理</param>
        /// <returns>受影响的行数/或第一行第一列的值</returns>
        public static object ExecuteScalar(this Database db, CommandType type, string commandText, ParameterCollection parameters, DbTransaction transaction)
        {
            DbCommand command = GetCommand(db, type, commandText);
            if (parameters != null)
            {
                command.Parameters.AddRange(parameters.ToArray());
            }
            return db.ExecuteScalar(command, transaction);
        }

        private static DbCommand GetCommand(Database db, CommandType type, string commandText)
        {
            switch (type)
            {
                case CommandType.StoredProcedure:
                    return db.GetStoredProcCommand(commandText);
                default:
                    return db.GetSqlStringCommand(commandText);
            }
        }
    }
}
